{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "merge  通过键拼接列，该函数的典型应用场景是，针对同一个主键存在两张包含不同字段的表，现在我们想把他们整合到一张表里。在此典型情况下，结果集的行数并没有增加，列数则为两个元数据的列数和减去连接键的数量。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>positionId</th>\n      <th>city</th>\n      <th>companyId</th>\n      <th>education</th>\n      <th>bottom</th>\n      <th>top</th>\n      <th>companyFullName</th>\n      <th>companyLabelList</th>\n      <th>companyShortName</th>\n      <th>companySize</th>\n      <th>businessZones</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2245819</td>\n      <td>上海</td>\n      <td>130876</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>3</td>\n      <td>上海银基富力信息技术有限公司</td>\n      <td>['年底双薪', '通讯津贴', '定期体检', '绩效奖金']</td>\n      <td>银基富力</td>\n      <td>15-50人</td>\n      <td>['上海影城', '新华路', '虹桥']</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1605795</td>\n      <td>上海</td>\n      <td>58109</td>\n      <td>本科</td>\n      <td>2</td>\n      <td>4</td>\n      <td>五五海淘（上海）科技股份有限公司</td>\n      <td>['股票期权', '带薪年假', '绩效奖金', '岗位晋升']</td>\n      <td>55海淘</td>\n      <td>150-500人</td>\n      <td>['漕宝路', '万源城', '东兰路']</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2392372</td>\n      <td>北京</td>\n      <td>48294</td>\n      <td>硕士</td>\n      <td>4</td>\n      <td>8</td>\n      <td>上海如旺电子商务有限公司</td>\n      <td>['年底双薪', '节日礼物', '技能培训', '免费班车']</td>\n      <td>旺旺集团火热招聘</td>\n      <td>50-150人</td>\n      <td>['虹桥', '古北', '虹梅路']</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2527100</td>\n      <td>上海</td>\n      <td>57577</td>\n      <td>本科</td>\n      <td>3</td>\n      <td>4</td>\n      <td>上海清源绿网科技有限公司</td>\n      <td>['节日礼物', '带薪年假', '绩效奖金', '扁平管理']</td>\n      <td>清源大数据</td>\n      <td>15-50人</td>\n      <td>['张江']</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2531473</td>\n      <td>上海</td>\n      <td>7069</td>\n      <td>本科</td>\n      <td>4</td>\n      <td>6</td>\n      <td>伽蓝（集团）股份有限公司</td>\n      <td>['绩效奖金', '年底双薪', '五险一金', '通讯津贴']</td>\n      <td>伽蓝</td>\n      <td>2000人以上</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   positionId city  companyId education  bottom top   companyFullName  \\\n0     2245819   上海     130876        本科       2   3    上海银基富力信息技术有限公司   \n1     1605795   上海      58109        本科       2   4  五五海淘（上海）科技股份有限公司   \n2     2392372   北京      48294        硕士       4   8      上海如旺电子商务有限公司   \n3     2527100   上海      57577        本科       3   4      上海清源绿网科技有限公司   \n4     2531473   上海       7069        本科       4   6      伽蓝（集团）股份有限公司   \n\n                   companyLabelList companyShortName companySize  \\\n0  ['年底双薪', '通讯津贴', '定期体检', '绩效奖金']             银基富力      15-50人   \n1  ['股票期权', '带薪年假', '绩效奖金', '岗位晋升']             55海淘    150-500人   \n2  ['年底双薪', '节日礼物', '技能培训', '免费班车']         旺旺集团火热招聘     50-150人   \n3  ['节日礼物', '带薪年假', '绩效奖金', '扁平管理']            清源大数据      15-50人   \n4  ['绩效奖金', '年底双薪', '五险一金', '通讯津贴']               伽蓝     2000人以上   \n\n           businessZones  \n0  ['上海影城', '新华路', '虹桥']  \n1  ['漕宝路', '万源城', '东兰路']  \n2    ['虹桥', '古北', '虹梅路']  \n3                 ['张江']  \n4                    NaN  "
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\r\n",
    "position = pd.read_csv('D:\\study_notes\\master\\data\\dataAnalyst_sql.csv')\r\n",
    "company = pd.read_csv('D:\\study_notes\\master\\data\\company_sql.csv')\r\n",
    "position.merge(right=company,how='inner',on='companyId').head()#inner表示只合并交集部分\r\n",
    "# position.merge(right=company,how='inner',left_on='companyId',right_on='id').head()#如果合并的依据不一样，可以用left_on和right_on区分"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "join 拼接列，主要用于索引上的合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>name</th>\n      <th>age</th>\n      <th>cp</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>0</td>\n      <td>lxh</td>\n      <td>20</td>\n      <td>lm</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>1</td>\n      <td>xiao</td>\n      <td>40</td>\n      <td>ly</td>\n    </tr>\n    <tr>\n      <th>c</th>\n      <td>2</td>\n      <td>hua</td>\n      <td>4</td>\n      <td>yry</td>\n    </tr>\n    <tr>\n      <th>d</th>\n      <td>3</td>\n      <td>be</td>\n      <td>70</td>\n      <td>old</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   id  name  age   cp\na   0   lxh   20   lm\nb   1  xiao   40   ly\nc   2   hua    4  yry\nd   3    be   70  old"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data=pd.DataFrame(\r\n",
    "    [{\"id\":0,\"name\":'lxh',\"age\":20,\"cp\":'lm'},\r\n",
    "     {\"id\":1,\"name\":'xiao',\"age\":40,\"cp\":'ly'},\r\n",
    "     {\"id\":2,\"name\":'hua',\"age\":4,\"cp\":'yry'},\r\n",
    "     {\"id\":3,\"name\":'be',\"age\":70,\"cp\":'old'}],\r\n",
    "        index=['a','b','c','d']\r\n",
    "    )\r\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>sex</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>a</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>b</th>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>e</th>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "   sex\na    0\nb    1\ne    2"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1=pd.DataFrame([{\"sex\":0},{\"sex\":1},{\"sex\":2}],index=['a','b','e'])\r\n",
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用默认的左连接\n",
      "    id  name  age   cp  sex\n",
      "a   0   lxh   20   lm  0.0\n",
      "b   1  xiao   40   ly  1.0\n",
      "c   2   hua    4  yry  NaN\n",
      "d   3    be   70  old  NaN\n"
     ]
    }
   ],
   "source": [
    "print('使用默认的左连接\\r\\n',data.join(data1))  #这里可以看出自动屏蔽了data中没有的index=e 那一行的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用右连接\n",
      "     id  name   age   cp  sex\n",
      "a  0.0   lxh  20.0   lm    0\n",
      "b  1.0  xiao  40.0   ly    1\n",
      "e  NaN   NaN   NaN  NaN    2\n"
     ]
    }
   ],
   "source": [
    "print('使用右连接\\r\\n',data.join(data1,how=\"right\")) #这里出自动屏蔽了data1中没有index=c,d的那行数据；等价于data1.join(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用内连接\n",
      "    id  name  age  cp  sex\n",
      "a   0   lxh   20  lm    0\n",
      "b   1  xiao   40  ly    1\n"
     ]
    }
   ],
   "source": [
    "print('使用内连接\\r\\n',data.join(data1,how='inner'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用全外连接\n",
      "     id  name   age   cp  sex\n",
      "a  0.0   lxh  20.0   lm  0.0\n",
      "b  1.0  xiao  40.0   ly  1.0\n",
      "c  2.0   hua   4.0  yry  NaN\n",
      "d  3.0    be  70.0  old  NaN\n",
      "e  NaN   NaN   NaN  NaN  2.0\n"
     ]
    }
   ],
   "source": [
    "print('使用全外连接\\r\\n',data.join(data1,how='outer'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Chicago</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>New York City</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            city  rank\n0        Chicago     1\n1  San Francisco     2\n2  New York City     3"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'city': ['Chicago', 'San Francisco', 'New York City'], 'rank': range(1, 4)})\r\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Chicago</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Boston</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Los Angeles</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "          city  rank\n0      Chicago     1\n1       Boston     4\n2  Los Angeles     5"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame({'city': ['Chicago', 'Boston', 'Los Angeles'], 'rank': [1, 4, 5]})\r\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按轴进行内连接\n",
      "             city  rank         city  rank\n",
      "0        Chicago     1      Chicago     1\n",
      "1  San Francisco     2       Boston     4\n",
      "2  New York City     3  Los Angeles     5\n"
     ]
    }
   ],
   "source": [
    "print('按轴进行内连接\\r\\n',pd.concat([df1,df2],join=\"inner\",axis=1))#暴力堆叠"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "进行外连接并指定keys(行索引)\n",
      "               city  rank\n",
      "a 0        Chicago     1\n",
      "  1  San Francisco     2\n",
      "  2  New York City     3\n",
      "b 0        Chicago     1\n",
      "  1         Boston     4\n",
      "  2    Los Angeles     5\n"
     ]
    }
   ],
   "source": [
    "print('进行外连接并指定keys(行索引)\\r\\n',pd.concat([df1,df2],keys=['a','b'])) #这里有重复的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "去重后\n",
      "             city  rank\n",
      "0        Chicago     1\n",
      "1  San Francisco     2\n",
      "2  New York City     3\n",
      "4         Boston     4\n",
      "5    Los Angeles     5\n"
     ]
    }
   ],
   "source": [
    "print('去重后\\r\\n',pd.concat([df1,df2],ignore_index=True).drop_duplicates())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>city</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Chicago</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>New York City</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Boston</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Los Angeles</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "            city  rank\n0        Chicago     1\n1  San Francisco     2\n2  New York City     3\n4         Boston     4\n5    Los Angeles     5"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.concat([df1,df2],ignore_index=True).drop_duplicates()\r\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "['city', 'rank']"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col = list(df.columns)\r\n",
    "col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>keyID</th>\n      <th>rank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Chicago</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>San Francisco</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>New York City</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Boston</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Los Angeles</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
      "text/plain": "           keyID  rank\n0        Chicago     1\n1  San Francisco     2\n2  New York City     3\n4         Boston     4\n5    Los Angeles     5"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col[0]='keyID'\r\n",
    "df.columns=col\r\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "name": "python385jvsc74a57bd0ae7890921ac3c17143ff000ac7152addc6614e2051824da39aa37dab63d26d82"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 2
 },
 "nbformat": 4,
 "nbformat_minor": 2
}